package com.practice.springboot_01_zwj.scheduled;

import com.practice.springboot_01_zwj.entity.IncomeQuestion;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Base64;
import java.util.List;

@Service
public class IncomeQuestionImportService {

    private static final Logger logger = LoggerFactory.getLogger(IncomeQuestionImportService.class);
    private static final int BATCH_SIZE = 100;
    private static final int REQUIRED_FIELD_COUNT = 81;
    private static final SimpleDateFormat[] DATE_FORMATS = {
            new SimpleDateFormat("yyyy/MM/dd"),
            new SimpleDateFormat("yyyy-MM-dd"),
            new SimpleDateFormat("dd/MM/yyyy")
    };

    // 字段索引常量
    private static final int BUSI_OPPORTUNITY_NUMBER = 0;
    private static final int BUSI_OPPORTUNITY_CODE = 1;
    private static final int GROUP_PROJECT_CODE = 2;
    private static final int BUSI_OPPORTUNITY_NAME = 3;
    private static final int PROJECT_NAME = 4;
    private static final int CUSTOMER_NAME = 5;
    private static final int PROVINCE_CODE = 6;
    private static final int CITY_NAME = 7;
    private static final int CITY_CODE = 8;
    private static final int PUBLIC_COUNTY_CODE = 9;
    private static final int INDUSTRY_COUNTY = 10;
    private static final int PARTNER_NAME = 11;
    private static final int BID_FLAG = 12;
    private static final int SJ_FIRST_TIME = 13;
    private static final int PRE_SALE_SUPPORT_LAUNCH_TIME = 14;
    private static final int PRE_SALE_SUPPORT_START_TIME = 15;
    private static final int PRE_SALE_SUPPORT_FINISH_TIME = 16;
    private static final int PRE_PROJECT_DATE = 17;
    private static final int WINNING_TIME = 18;
    private static final int PROJECT_FINISH_TIME = 19;
    private static final int PRO_SIGNING_FIRST_TIME = 20;
    private static final int ACT_START_TIME = 21;
    private static final int ACTUAL_DELIVERY_TIME = 22;
    private static final int CONTRACT_DELIVERY_TIME = 23;
    private static final int CONTRACT_BEGIN_TIME = 24;
    private static final int CONTRACT_END_TIME = 25;
    private static final int MONTH_PLAN_BILL_AMOUNT_HAN = 26;
    private static final int MONTH_PLAN_BILL_AMOUNT_NONE = 27;
    private static final int MONTH_ACTUAL_BILL_AMOUNT_HAN = 28;
    private static final int MONTH_ACTUAL_BILL_AMOUNT_NONE = 29;
    private static final int MONTH_PLAN_EXPENSE_AMOUNT_HAN = 30;
    private static final int MONTH_PLAN_EXPENSE_AMOUNT_NONE = 31;
    private static final int MONTH_ACTUAL_REPORT_AMOUNT_HAN = 32;
    private static final int MONTH_ACTUAL_REPORT_AMOUNT_NONE = 33;
    private static final int WIN_FLAG = 34;
    private static final int JF_FLAG = 35;
    private static final int JD_FLAG = 36;
    private static final int QY_FLAG = 37;
    private static final int CONTRACT_NAME = 38;
    private static final int CONTRACT_CODE = 39;
    private static final int AFTER_CONTRACT_NAME = 40;
    private static final int AFTER_CONTRACT_CODE = 41;
    private static final int MONEY_TAX = 42;
    private static final int WIN_BID_MONEY = 43;
    private static final int AFTER_CONTRACT_AMOUNT = 44;
    private static final int AFTER_CONTRACT_AMOUNT_NONE = 45;
    private static final int IT_SERVICE_REVENUE_AMOUNT = 46;
    private static final int IT_SERVICE_REVENUE_AMOUNT_NONE = 47;
    private static final int CT_SERVICE_REVENUE_AMOUNT = 48;
    private static final int CT_SERVICE_REVENUE_AMOUNT_NONE = 49;
    private static final int NON_SERVICE_REVENUE_AMOUNT = 50;
    private static final int NON_SERVICE_REVENUE_AMOUNT_NONE = 51;
    private static final int CURRENT_STAGE = 52;
    private static final int JX_FLAG = 53;
    private static final int XQ_STATE = 54;
    private static final int PROJECT_CONTRACT_CODE = 55;
    private static final int PROJ_IMPLEMENTATION_TYPE = 56;
    private static final int TOTAL_PROFIT = 57;
    private static final int IT_PROFIT = 58;
    private static final int PROFIT_MARGIN = 59;
    private static final int IT_NPV = 60;
    private static final int PROFIT_MARGIN_HE = 61;
    private static final int IT_IPR = 62;
    private static final int DYNAMIC_YEAR = 63;
    private static final int STATIC_YEAR = 64;
    private static final int UNIQUE_PROJECT_INST_ID = 65;
    private static final int BUP_MENU_NO = 66;
    private static final int TABLE_RECORD = 67;
    private static final int IT_SERVICE_REVENUE_AMOUNT_NONEYS = 68;
    private static final int CT_SERVICE_REVENUE_AMOUNT_NONEYS = 69;
    private static final int NON_SERVICE_REVENUE_AMOUNT_NONEYS = 70;
    private static final int ITT_FOU_TTAX_AMOUNT25 = 71;
    private static final int CTT_FOU_TTAX_AMOUNT25 = 72;
    private static final int FT_FOU_TTAX_AMOUNT25 = 73;
    private static final int ITT_FOU_TTAX_AMOUNT24 = 74;
    private static final int CTT_FOU_TTAX_AMOUNT24 = 75;
    private static final int FT_FOU_TTAX_AMOUNT24 = 76;
    private static final int ITT_FOU_TTAX_AMOUNT23 = 77;
    private static final int CTT_FOU_TTAX_AMOUNT23 = 78;
    private static final int FT_FOU_TTAX_AMOUNT23 = 79;
    private static final int OPP_CREATE_DATE = 80;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 主导入方法 - 优化版本
     */
    @Transactional
    public void importLargeFile(String filePath) {
        long startTime = System.currentTimeMillis();
        logger.info("开始导入文件：{}", filePath);

        // 检查数据库字段长度
        checkColumnLengths();

        // 删除原有数据
        logger.info("开始删除表中原有数据...");
        long deleteStart = System.currentTimeMillis();
        int deletedRows = jdbcTemplate.update("DELETE FROM gzt_income_mgr_question");
        logger.info("已删除 {} 条原有数据，耗时：{}ms", deletedRows, System.currentTimeMillis() - deleteStart);

        List<IncomeQuestion> buffer = new ArrayList<>(BATCH_SIZE);
        int totalLines = 0;
        int successCount = 0;
        int skippedHeader = 0;

        try (BufferedReader br = new BufferedReader(
                new InputStreamReader(new FileInputStream(filePath), StandardCharsets.UTF_8), 16384)) {

            String line;
            boolean isFirstLine = true;

            while ((line = br.readLine()) != null) {
                // 跳过第一行（标题行）
                if (isFirstLine) {
                    logger.info("跳过标题行：{}", truncateString(line, 100));
                    isFirstLine = false;
                    skippedHeader++;
                    continue;
                }

                // 跳过空行
                if (line.trim().isEmpty()) {
                    continue;
                }

                totalLines++;
                try {
                    IncomeQuestion entity = parseLine(line, totalLines);
                    if (entity != null) {
                        buffer.add(entity);
                        successCount++;
                    }

                    // 批量插入
                    if (buffer.size() >= BATCH_SIZE) {
                        executeBatchInsertWithValidation(buffer);
                        buffer.clear();
                    }
                } catch (Exception e) {
                    logger.error("第 {} 行解析失败：{} | 原始行：{}",
                            totalLines, e.getMessage(), truncateString(line, 100));
                }
            }

            // 插入剩余数据
            if (!buffer.isEmpty()) {
                executeBatchInsertWithValidation(buffer);
            }

        } catch (Exception e) {
            logger.error("文件读取失败", e);
            throw new RuntimeException("文件导入失败", e);
        }

        logger.info("导入完成，跳过标题行：{}，数据行数：{}，成功：{}，总耗时：{}ms",
                skippedHeader, totalLines, successCount, System.currentTimeMillis() - startTime);
    }

    /**
     * 检查数据库字段实际长度
     */
    private void checkColumnLengths() {
        try {
            String sql = "SELECT " +
                    "CHARACTER_MAXIMUM_LENGTH as max_length, " +
                    "COLUMN_NAME as column_name " +
                    "FROM INFORMATION_SCHEMA.COLUMNS " +
                    "WHERE TABLE_NAME = 'gzt_income_mgr_question' " +
                    "AND TABLE_SCHEMA = DATABASE() " +
                    "AND DATA_TYPE IN ('varchar', 'char', 'text')";

            jdbcTemplate.query(sql, (rs) -> {
                String columnName = rs.getString("column_name");
                int maxLength = rs.getInt("max_length");
                logger.info("字段 {} 的最大长度: {}", columnName, maxLength);
            });
        } catch (Exception e) {
            logger.warn("无法获取字段长度信息: {}", e.getMessage());
        }
    }

    /**
     * 日期解析方法 - 返回 java.util.Date
     */
    private java.util.Date parseDate(String value) {
        if (value == null || value.trim().isEmpty()) {
            return null;
        }

        String cleanValue = value.replaceAll("[\"']", "").trim();

        if (cleanValue.equalsIgnoreCase("null") || cleanValue.equals("-")) {
            return null;
        }

        for (SimpleDateFormat format : DATE_FORMATS) {
            try {
                format.setLenient(false);
                return format.parse(cleanValue); // 返回 java.util.Date
            } catch (java.text.ParseException e) {
                // 继续尝试下一个格式
            }
        }

        logger.warn("日期解析失败：{}", cleanValue);
        return null;
    }

    /**
     * 字段提取方法
     */
    private List<String> extractFields(String line) {
        List<String> fields = new ArrayList<>(REQUIRED_FIELD_COUNT);
        String[] parts = line.split("\\|", -1);

        for (String part : parts) {
            String field = part.replaceAll("^\"|\"$", "").trim();
            fields.add(field.isEmpty() ? null : field);
        }

        while (fields.size() < REQUIRED_FIELD_COUNT) {
            fields.add(null);
        }

        return fields.subList(0, REQUIRED_FIELD_COUNT);
    }

    /**
     * 解析单行数据 - 优化版本
     */
    private IncomeQuestion parseLine(String line, int lineNumber) {
        if (line.trim().isEmpty()) {
            logger.warn("第 {} 行为空行，已跳过", lineNumber);
            return null;
        }

        List<String> fields = extractFields(line);

        if (fields.size() != REQUIRED_FIELD_COUNT) {
            throw new IllegalArgumentException(
                    String.format("字段数量错误（实际：%d，需要：%d）", fields.size(), REQUIRED_FIELD_COUNT));
        }

        IncomeQuestion entity = new IncomeQuestion();
        try {
            // 字符串字段 - 全部添加长度验证
            entity.setBusiOpportunityNumber(validateStringLength(fields.get(BUSI_OPPORTUNITY_NUMBER), 500, "busiOpportunityNumber", lineNumber));
            entity.setBusiOpportunityCode(validateStringLength(fields.get(BUSI_OPPORTUNITY_CODE), 500, "busiOpportunityCode", lineNumber));
            entity.setGroupProjectCode(validateStringLength(fields.get(GROUP_PROJECT_CODE), 1000, "groupProjectCode", lineNumber));
            entity.setBusiOpportunityName(validateStringLength(fields.get(BUSI_OPPORTUNITY_NAME), 1000, "busiOpportunityName", lineNumber));
            entity.setProjectName(validateStringLength(fields.get(PROJECT_NAME), 1000, "projectName", lineNumber));

            // 客户名称加密
            String customerNameValue = validateStringLength(fields.get(CUSTOMER_NAME), 500, "customerName", lineNumber);
            if (customerNameValue != null) {
                customerNameValue = encryptWithBase64(customerNameValue);
            }
            entity.setCustomerName(customerNameValue);

            entity.setProvinceCode(validateStringLength(fields.get(PROVINCE_CODE), 100, "provinceCode", lineNumber));
            entity.setCityName(validateStringLength(fields.get(CITY_NAME), 100, "cityName", lineNumber));
            entity.setCityCode(validateStringLength(fields.get(CITY_CODE), 100, "cityCode", lineNumber));
            entity.setPublicCountyCode(validateStringLength(fields.get(PUBLIC_COUNTY_CODE), 100, "publicCountyCode", lineNumber));
            entity.setIndustrycounty(validateStringLength(fields.get(INDUSTRY_COUNTY), 100, "industrycounty", lineNumber));
            entity.setPartnerName(validateStringLength(fields.get(PARTNER_NAME), 500, "partnerName", lineNumber));
            entity.setBidFlag(validateStringLength(fields.get(BID_FLAG), 50, "bidFlag", lineNumber));
            entity.setWinFlag(validateStringLength(fields.get(WIN_FLAG), 50, "winFlag", lineNumber));
            entity.setJfFlag(validateStringLength(fields.get(JF_FLAG), 50, "jfFlag", lineNumber));
            entity.setJdFlag(validateStringLength(fields.get(JD_FLAG), 50, "jdFlag", lineNumber));
            entity.setQyFlag(validateStringLength(fields.get(QY_FLAG), 50, "qyFlag", lineNumber));
            entity.setContractName(validateStringLength(fields.get(CONTRACT_NAME), 1600, "contractName", lineNumber));
            entity.setContractCode(validateStringLength(fields.get(CONTRACT_CODE), 500, "contractCode", lineNumber));
            entity.setAfterContractName(validateStringLength(fields.get(AFTER_CONTRACT_NAME), 1000, "afterContractName", lineNumber));
            entity.setAfterContractCode(validateStringLength(fields.get(AFTER_CONTRACT_CODE), 500, "afterContractCode", lineNumber));
            entity.setCurrentStage(validateStringLength(fields.get(CURRENT_STAGE), 100, "currentStage", lineNumber));
            entity.setJxFlag(validateStringLength(fields.get(JX_FLAG), 50, "jxFlag", lineNumber));
            entity.setXqState(validateStringLength(fields.get(XQ_STATE), 100, "xqState", lineNumber));
            entity.setProjectContractCode(validateStringLength(fields.get(PROJECT_CONTRACT_CODE), 500, "projectContractCode", lineNumber));
            entity.setProjImplementationType(validateStringLength(fields.get(PROJ_IMPLEMENTATION_TYPE), 100, "projImplementationType", lineNumber));
            entity.setUniqueProjectInstId(validateStringLength(fields.get(UNIQUE_PROJECT_INST_ID), 500, "uniqueProjectInstId", lineNumber));
            entity.setBupMenuNo(validateStringLength(fields.get(BUP_MENU_NO), 500, "bupMenuNo", lineNumber));
            entity.setTableRecord(validateStringLength(fields.get(TABLE_RECORD), 500, "tableRecord", lineNumber));

            // 日期字段 - 使用 java.util.Date
            entity.setSjFirstTime(parseDate(fields.get(SJ_FIRST_TIME)));
            entity.setPreSaleSupportLaunchTime(parseDate(fields.get(PRE_SALE_SUPPORT_LAUNCH_TIME)));
            entity.setPreSaleSupportStartTime(parseDate(fields.get(PRE_SALE_SUPPORT_START_TIME)));
            entity.setPreSaleSupportFinishTime(parseDate(fields.get(PRE_SALE_SUPPORT_FINISH_TIME)));
            entity.setPreProjectDate(parseDate(fields.get(PRE_PROJECT_DATE)));
            entity.setWinningTime(parseDate(fields.get(WINNING_TIME)));
            entity.setProjectFinishTime(parseDate(fields.get(PROJECT_FINISH_TIME)));
            entity.setProSigningFirstTime(parseDate(fields.get(PRO_SIGNING_FIRST_TIME)));
            entity.setActStartTime(parseDate(fields.get(ACT_START_TIME)));
            entity.setActualDeliveryTime(parseDate(fields.get(ACTUAL_DELIVERY_TIME)));
            entity.setContractDeliveryTime(parseDate(fields.get(CONTRACT_DELIVERY_TIME)));
            entity.setContractBeginTime(parseDate(fields.get(CONTRACT_BEGIN_TIME)));
            entity.setContractEndTime(parseDate(fields.get(CONTRACT_END_TIME)));
            entity.setOppcreatedate(parseDate(fields.get(OPP_CREATE_DATE)));

            // 数值字段
            entity.setMonthPlanBillAmountHan(safeParseDouble(fields.get(MONTH_PLAN_BILL_AMOUNT_HAN)));
            entity.setMonthPlanBillAmountNone(safeParseDouble(fields.get(MONTH_PLAN_BILL_AMOUNT_NONE)));
            entity.setMonthActualBillAmountHan(safeParseDouble(fields.get(MONTH_ACTUAL_BILL_AMOUNT_HAN)));
            entity.setMonthActualBillAmountNone(safeParseDouble(fields.get(MONTH_ACTUAL_BILL_AMOUNT_NONE)));
            entity.setMonthPlanExpenseAmountHan(safeParseDouble(fields.get(MONTH_PLAN_EXPENSE_AMOUNT_HAN)));
            entity.setMonthPlanExpenseAmountNone(safeParseDouble(fields.get(MONTH_PLAN_EXPENSE_AMOUNT_NONE)));
            entity.setMonthActualReportAmountHan(safeParseDouble(fields.get(MONTH_ACTUAL_REPORT_AMOUNT_HAN)));
            entity.setMonthActualReportAmountNone(safeParseDouble(fields.get(MONTH_ACTUAL_REPORT_AMOUNT_NONE)));
            entity.setMoneyTax(safeParseDouble(fields.get(MONEY_TAX)));
            entity.setWinBidMoney(safeParseDouble(fields.get(WIN_BID_MONEY)));
            entity.setAfterContractAmount(safeParseDouble(fields.get(AFTER_CONTRACT_AMOUNT)));
            entity.setAfterContractAmountNone(safeParseDouble(fields.get(AFTER_CONTRACT_AMOUNT_NONE)));
            entity.setItServiceRevenueAmount(safeParseDouble(fields.get(IT_SERVICE_REVENUE_AMOUNT)));
            entity.setItServiceRevenueAmountNone(safeParseDouble(fields.get(IT_SERVICE_REVENUE_AMOUNT_NONE)));
            entity.setCtServiceRevenueAmount(safeParseDouble(fields.get(CT_SERVICE_REVENUE_AMOUNT)));
            entity.setCtServiceRevenueAmountNone(safeParseDouble(fields.get(CT_SERVICE_REVENUE_AMOUNT_NONE)));
            entity.setNonServiceRevenueAmount(safeParseDouble(fields.get(NON_SERVICE_REVENUE_AMOUNT)));
            entity.setNonServiceRevenueAmountNone(safeParseDouble(fields.get(NON_SERVICE_REVENUE_AMOUNT_NONE)));
            entity.setTotalProfit(safeParseDouble(fields.get(TOTAL_PROFIT)));
            entity.setItProfit(safeParseDouble(fields.get(IT_PROFIT)));
            entity.setProfitMargin(safeParseDouble(fields.get(PROFIT_MARGIN)));
            entity.setItNpv(safeParseDouble(fields.get(IT_NPV)));
            entity.setProfitMarginHe(safeParseDouble(fields.get(PROFIT_MARGIN_HE)));
            entity.setItIpr(safeParseDouble(fields.get(IT_IPR)));
            entity.setDynamicYear(safeParseDouble(fields.get(DYNAMIC_YEAR)));
            entity.setStaticYear(safeParseDouble(fields.get(STATIC_YEAR)));
            entity.setItservicerevenueamountnoneys(safeParseDouble(fields.get(IT_SERVICE_REVENUE_AMOUNT_NONEYS)));
            entity.setCtservicerevenueamountnoneys(safeParseDouble(fields.get(CT_SERVICE_REVENUE_AMOUNT_NONEYS)));
            entity.setNonservicerevenueamountnoneys(safeParseDouble(fields.get(NON_SERVICE_REVENUE_AMOUNT_NONEYS)));
            entity.setIttfouttaxamount25(safeParseDouble(fields.get(ITT_FOU_TTAX_AMOUNT25)));
            entity.setCttfouttaxamount25(safeParseDouble(fields.get(CTT_FOU_TTAX_AMOUNT25)));
            entity.setFtfouttaxamount25(safeParseDouble(fields.get(FT_FOU_TTAX_AMOUNT25)));
            entity.setIttfouttaxamount24(safeParseDouble(fields.get(ITT_FOU_TTAX_AMOUNT24)));
            entity.setCttfouttaxamount24(safeParseDouble(fields.get(CTT_FOU_TTAX_AMOUNT24)));
            entity.setFtfouttaxamount24(safeParseDouble(fields.get(FT_FOU_TTAX_AMOUNT24)));
            entity.setIttfouttaxamount23(safeParseDouble(fields.get(ITT_FOU_TTAX_AMOUNT23)));
            entity.setCttfouttaxamount23(safeParseDouble(fields.get(CTT_FOU_TTAX_AMOUNT23)));
            entity.setFtfouttaxamount23(safeParseDouble(fields.get(FT_FOU_TTAX_AMOUNT23)));

        } catch (Exception e) {
            throw new IllegalArgumentException("字段映射失败：" + e.getMessage());
        }
        return entity;
    }

    /**
     * 增强的字符串长度验证方法
     */
    private String validateStringLength(String value, int maxLength, String fieldName, int lineNumber) {
        String parsed = parseString(value);
        if (parsed != null && parsed.length() > maxLength) {
            logger.warn("第 {} 行 {} 值过长 ({} 字符)，将被截断: {}",
                    lineNumber, fieldName, parsed.length(),
                    truncateString(parsed, 100));

            String truncated = parsed.substring(0, Math.min(parsed.length(), maxLength));

            if (truncated.length() > maxLength) {
                logger.error("截断后仍然超长，强制截断到最大长度: {}", maxLength);
                truncated = truncated.substring(0, maxLength);
            }

            return truncated;
        }
        return parsed;
    }

    /**
     * 数值转换方法
     */
    private Double safeParseDouble(String value) {
        if (value == null || value.trim().isEmpty()) return null;

        try {
            String cleanValue = value.replaceAll("[^\\d.-]", "").replaceAll(",", "");
            return cleanValue.isEmpty() ? null : Double.parseDouble(cleanValue);
        } catch (NumberFormatException e) {
            logger.warn("数值转换失败：{}", value);
            return null;
        }
    }

    /**
     * 字符串处理
     */
    private String parseString(String value) {
        return (value == null || value.equalsIgnoreCase("null") || value.trim().isEmpty()) ? null : value.trim();
    }

    /**
     * 批量插入前的数据验证
     */
    private void validateBeforeBatchInsert(List<IncomeQuestion> entities) {
        for (int i = 0; i < entities.size(); i++) {
            IncomeQuestion entity = entities.get(i);

            // 验证关键字段长度
            if (entity.getContractName() != null && entity.getContractName().length() > 1600) {
                logger.error("批量插入前验证失败：第 {} 条记录的 contractName 长度 {} 超过限制",
                        i, entity.getContractName().length());
                entity.setContractName(entity.getContractName().substring(0, 1600));
            }
        }
    }

    /**
     * 执行批量插入 - 优化版本
     */
    @Transactional(propagation = Propagation.REQUIRED)
    void executeBatchInsertWithValidation(List<IncomeQuestion> entities) {
        // 批量插入前的最终验证
        validateBeforeBatchInsert(entities);

        String sql = "INSERT INTO gzt_income_mgr_question (" +
                "busiOpportunityNumber,busiOpportunityCode,groupProjectCode,busiOpportunityName,projectName," +
                "customerName,provinceCode,cityName,cityCode,publicCountyCode,industrycounty,partnerName," +
                "bidFlag,sjFirstTime,preSaleSupportLaunchTime,preSaleSupportStartTime,preSaleSupportFinishTime," +
                "preProjectDate,winningTime,projectFinishTime,proSigningFirstTime,actStartTime,actualDeliveryTime," +
                "contractDeliveryTime,contractBeginTime,contractEndTime,monthPlanBillAmountHan,monthPlanBillAmountNone," +
                "monthActualBillAmountHan,monthActualBillAmountNone,monthPlanExpenseAmountHan,monthPlanExpenseAmountNone," +
                "monthActualReportAmountHan,monthActualReportAmountNone,winFlag,jfFlag,jdFlag,qyFlag,contractName," +
                "contractCode,afterContractName,afterContractCode,moneyTax,winBidMoney,afterContractAmount," +
                "afterContractAmountNone,itServiceRevenueAmount,itServiceRevenueAmountNone,ctServiceRevenueAmount," +
                "ctServiceRevenueAmountNone,nonServiceRevenueAmount,nonServiceRevenueAmountNone,currentStage,jxFlag," +
                "xqState,projectContractCode,projImplementationType,totalProfit,itProfit,profitMargin,itNpv," +
                "profitMarginHe,itIpr,dynamicYear,staticYear,UniqueProjectInstId,bupMenuNo,tableRecord," +
                "itservicerevenueamountnoneys,ctservicerevenueamountnoneys,nonservicerevenueamountnoneys," +
                "ittfouttaxamount25,cttfouttaxamount25,ftfouttaxamount25,ittfouttaxamount24,cttfouttaxamount24," +
                "ftfouttaxamount24,ittfouttaxamount23,cttfouttaxamount23,ftfouttaxamount23,oppcreatedate" +
                ") VALUES (" + getPlaceholders(81) + ")";

        long batchStartTime = System.currentTimeMillis();

        try {
            jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    IncomeQuestion entity = entities.get(i);
                    int index = 1;

                    try {
                        // 字符串字段 - 使用安全设置
                        ps.setString(index++, safeSetString(entity.getBusiOpportunityNumber(), 500));
                        ps.setString(index++, safeSetString(entity.getBusiOpportunityCode(), 500));
                        ps.setString(index++, safeSetString(entity.getGroupProjectCode(), 1000));
                        ps.setString(index++, safeSetString(entity.getBusiOpportunityName(), 1000));
                        ps.setString(index++, safeSetString(entity.getProjectName(), 1000));
                        ps.setString(index++, safeSetString(entity.getCustomerName(), 500));
                        ps.setString(index++, safeSetString(entity.getProvinceCode(), 100));
                        ps.setString(index++, safeSetString(entity.getCityName(), 100));
                        ps.setString(index++, safeSetString(entity.getCityCode(), 100));
                        ps.setString(index++, safeSetString(entity.getPublicCountyCode(), 100));
                        ps.setString(index++, safeSetString(entity.getIndustrycounty(), 100));
                        ps.setString(index++, safeSetString(entity.getPartnerName(), 500));
                        ps.setString(index++, safeSetString(entity.getBidFlag(), 50));

                        // 日期字段 - 将 java.util.Date 转换为 java.sql.Date
                        setNullableDate(ps, index++, entity.getSjFirstTime());
                        setNullableDate(ps, index++, entity.getPreSaleSupportLaunchTime());
                        setNullableDate(ps, index++, entity.getPreSaleSupportStartTime());
                        setNullableDate(ps, index++, entity.getPreSaleSupportFinishTime());
                        setNullableDate(ps, index++, entity.getPreProjectDate());
                        setNullableDate(ps, index++, entity.getWinningTime());
                        setNullableDate(ps, index++, entity.getProjectFinishTime());
                        setNullableDate(ps, index++, entity.getProSigningFirstTime());
                        setNullableDate(ps, index++, entity.getActStartTime());
                        setNullableDate(ps, index++, entity.getActualDeliveryTime());
                        setNullableDate(ps, index++, entity.getContractDeliveryTime());
                        setNullableDate(ps, index++, entity.getContractBeginTime());
                        setNullableDate(ps, index++, entity.getContractEndTime());

                        // 数值字段
                        setNullableDouble(ps, index++, entity.getMonthPlanBillAmountHan());
                        setNullableDouble(ps, index++, entity.getMonthPlanBillAmountNone());
                        setNullableDouble(ps, index++, entity.getMonthActualBillAmountHan());
                        setNullableDouble(ps, index++, entity.getMonthActualBillAmountNone());
                        setNullableDouble(ps, index++, entity.getMonthPlanExpenseAmountHan());
                        setNullableDouble(ps, index++, entity.getMonthPlanExpenseAmountNone());
                        setNullableDouble(ps, index++, entity.getMonthActualReportAmountHan());
                        setNullableDouble(ps, index++, entity.getMonthActualReportAmountNone());

                        // 继续设置其他字段...
                        ps.setString(index++, safeSetString(entity.getWinFlag(), 50));
                        ps.setString(index++, safeSetString(entity.getJfFlag(), 50));
                        ps.setString(index++, safeSetString(entity.getJdFlag(), 50));
                        ps.setString(index++, safeSetString(entity.getQyFlag(), 50));
                        ps.setString(index++, safeSetString(entity.getContractName(), 1600));
                        ps.setString(index++, safeSetString(entity.getContractCode(), 500));
                        ps.setString(index++, safeSetString(entity.getAfterContractName(), 1000));
                        ps.setString(index++, safeSetString(entity.getAfterContractCode(), 500));

                        setNullableDouble(ps, index++, entity.getMoneyTax());
                        setNullableDouble(ps, index++, entity.getWinBidMoney());
                        setNullableDouble(ps, index++, entity.getAfterContractAmount());
                        setNullableDouble(ps, index++, entity.getAfterContractAmountNone());
                        setNullableDouble(ps, index++, entity.getItServiceRevenueAmount());
                        setNullableDouble(ps, index++, entity.getItServiceRevenueAmountNone());
                        setNullableDouble(ps, index++, entity.getCtServiceRevenueAmount());
                        setNullableDouble(ps, index++, entity.getCtServiceRevenueAmountNone());
                        setNullableDouble(ps, index++, entity.getNonServiceRevenueAmount());
                        setNullableDouble(ps, index++, entity.getNonServiceRevenueAmountNone());

                        ps.setString(index++, safeSetString(entity.getCurrentStage(), 100));
                        ps.setString(index++, safeSetString(entity.getJxFlag(), 50));
                        ps.setString(index++, safeSetString(entity.getXqState(), 100));
                        ps.setString(index++, safeSetString(entity.getProjectContractCode(), 500));
                        ps.setString(index++, safeSetString(entity.getProjImplementationType(), 100));

                        setNullableDouble(ps, index++, entity.getTotalProfit());
                        setNullableDouble(ps, index++, entity.getItProfit());
                        setNullableDouble(ps, index++, entity.getProfitMargin());
                        setNullableDouble(ps, index++, entity.getItNpv());
                        setNullableDouble(ps, index++, entity.getProfitMarginHe());
                        setNullableDouble(ps, index++, entity.getItIpr());
                        setNullableDouble(ps, index++, entity.getDynamicYear());
                        setNullableDouble(ps, index++, entity.getStaticYear());

                        ps.setString(index++, safeSetString(entity.getUniqueProjectInstId(), 500));
                        ps.setString(index++, safeSetString(entity.getBupMenuNo(), 500));
                        ps.setString(index++, safeSetString(entity.getTableRecord(), 500));

                        setNullableDouble(ps, index++, entity.getItservicerevenueamountnoneys());
                        setNullableDouble(ps, index++, entity.getCtservicerevenueamountnoneys());
                        setNullableDouble(ps, index++, entity.getNonservicerevenueamountnoneys());
                        setNullableDouble(ps, index++, entity.getIttfouttaxamount25());
                        setNullableDouble(ps, index++, entity.getCttfouttaxamount25());
                        setNullableDouble(ps, index++, entity.getFtfouttaxamount25());
                        setNullableDouble(ps, index++, entity.getIttfouttaxamount24());
                        setNullableDouble(ps, index++, entity.getCttfouttaxamount24());
                        setNullableDouble(ps, index++, entity.getFtfouttaxamount24());
                        setNullableDouble(ps, index++, entity.getIttfouttaxamount23());
                        setNullableDouble(ps, index++, entity.getCttfouttaxamount23());
                        setNullableDouble(ps, index++, entity.getFtfouttaxamount23());

                        setNullableDate(ps, index, entity.getOppcreatedate());

                    } catch (SQLException e) {
                        logger.error("设置第 {} 条记录参数时发生错误: {}", i, e.getMessage());
                        throw e;
                    }
                }

                @Override
                public int getBatchSize() {
                    return entities.size();
                }
            });

            long batchTime = System.currentTimeMillis() - batchStartTime;
            logger.debug("成功批量插入 {} 条记录，耗时：{}ms", entities.size(), batchTime);

        } catch (Exception e) {
            logger.error("批量插入失败: {}", e.getMessage());
            logFailedEntities(entities, e);
            throw new RuntimeException("批量插入失败", e);
        }
    }

    /**
     * 生成占位符
     */
    private String getPlaceholders(int count) {
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < count; i++) {
            if (i > 0) sb.append(",");
            sb.append("?");
        }
        return sb.toString();
    }

    /**
     * 安全的字符串设置方法
     */
    private String safeSetString(String value, int maxLength) {
        if (value == null) {
            return null;
        }
        if (value.length() > maxLength) {
            logger.warn("字符串长度 {} 超过限制 {}，将被截断", value.length(), maxLength);
            return value.substring(0, maxLength);
        }
        return value;
    }

    /**
     * 记录失败的数据
     */
    private void logFailedEntities(List<IncomeQuestion> entities, Exception e) {
        logger.error("批量插入失败，开始记录失败数据...");
        for (int i = 0; i < Math.min(entities.size(), 3); i++) {
            IncomeQuestion entity = entities.get(i);
            logger.error("失败数据 {}: contractName长度={}, projectName长度={}",
                    i,
                    entity.getContractName() != null ? entity.getContractName().length() : 0,
                    entity.getProjectName() != null ? entity.getProjectName().length() : 0);
        }
    }

    /**
     * 日期设置方法 - 处理 java.util.Date 到 java.sql.Date 的转换
     */
    private void setNullableDate(PreparedStatement ps, int index, java.util.Date value) throws SQLException {
        if (value != null) {
            // 将 java.util.Date 转换为 java.sql.Date
            ps.setDate(index, new java.sql.Date(value.getTime()));
        } else {
            ps.setNull(index, java.sql.Types.DATE);
        }
    }

    private void setNullableDouble(PreparedStatement ps, int index, Double value) throws SQLException {
        if (value != null) {
            ps.setDouble(index, value);
        } else {
            ps.setNull(index, java.sql.Types.DOUBLE);
        }
    }

    private String truncateString(String input, int maxLength) {
        return input.length() > maxLength ? input.substring(0, maxLength) + "..." : input;
    }

    /**
     * Base64加密方法
     */
    private String encryptWithBase64(String data) {
        if (data == null) {
            return null;
        }
        try {
            return Base64.getEncoder().encodeToString(data.getBytes("UTF-8"));
        } catch (java.io.UnsupportedEncodingException e) {
            logger.warn("Base64加密失败，返回原值: {}", data);
            return data;
        }
    }
}